package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.RoadsignDto;
import com.hualude.roadsign.model.RoadsignEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by shao-yikun on 2017/4/9.
 */
public interface RoadSignRepository extends CrudRepository<RoadsignEntity, Long> {

    /**
     * @Description: 统计某标杆上标牌的个数
     * @Author: shao-yikun
     * @Date: 2017/3/6 19:09
     */
    @Query(value = "select count(*) from RoadsignEntity where roadPoleId = ?1")
    public Integer getRoadSignCountByRoadPole(Integer roadPoleId) throws Exception;

    /**
     * @Description: 标牌信息的DT展示
     * @Author: shao-yikun
     * @Date: 2017/3/4 12:41
     */
    public List<RoadsignDto> getRoadSignCollect4DT(DTRequestDto dtRequestDto,
                                                   Integer roadPoleId) throws Exception;

    /**
     * @Description: 根据标杆ID获取标牌
     * @Author: shao-yikun
     * @Date: 2017/7/11 下午8:50
     */
    @Query(value = "from RoadsignEntity where roadPoleId = ?1")
    public List<RoadsignEntity> getRoadSignByRoadPoleId(Integer roadPoleId) throws Exception;

    /**
     * @Description: 标牌信息的DT展示的数量
     * @Author: shao-yikun
     * @Date: 2017/3/4 12:45
     */
    @Query(value = "select count(*) from RoadsignEntity where roadPoleId = ?1")
    public Integer getCount4DT(Integer roadPoleId) throws Exception;

    /**
     * @Description: 补充或修改标牌信息
     * @Author: shao-yikun
     * @Date: 2017/3/5 14:01
     */
    @Modifying
    @Query(value = "update RoadsignEntity set roadSignDirection = ?2, roadSignType = ?3, " +
            "roadSignMeaning = ?4, roadSignShape = ?5, roadSignSize = ?6, roadSignPic = ?7, " +
            "roadSignMaterial = ?8, roadSignInstallType = ?9, roadSignFixed = ?10, " +
            "roadSignSecurity = ?11, roadSignBug = ?12, roadSignRemark = ?13, roadSignLight = ?14, " +
            "environment = ?15, defectsLiabilityPeriod = ?16, serviceLife = ?17, installOrg = ?18, " +
            "roadSignInstallTime = ?19, modifierId = ?20, roadSignIsComplete = 0 where roadSignId = ?1")
    public Integer updateRoadSignCollect(Integer roadSignId,
                                         Integer roadSignDirection,
                                         Integer roadSignType,
                                         Integer roadSignMeaning,
                                         Integer roadSignShape,
                                         String roadSignSize,
                                         String roadSignPic,
                                         Integer roadSignMaterial,
                                         Integer roadSignInstallType,
                                         Integer roadSignFixed,
                                         Integer roadSignSecurity,
                                         Integer roadSignBug,
                                         String roadSignRemark,
                                         Integer roadSignLight,
                                         Integer environment,
                                         Integer defectsLiabilityPeriod,
                                         Integer serviceLife,
                                         String installOrg,
                                         String roadSignInstallTime,
                                         Integer modifierId) throws Exception;

    /**
     * @Description: 删除标牌信息
     * @Author: shao-yikun
     * @Date: 2017/3/5 15:16
     */
    @Modifying
    @Query(value = "delete from RoadsignEntity where roadSignId = ?1")
    public Integer deleteRoadSignCollect(Integer roadSignId) throws Exception;

    /**
     * @Description: 标牌审核通过
     * @Author: jiang-weirong
     * @Date: 2017/4/10 16:06
     */
    @Modifying
    @Query(value = "update RoadsignEntity set  roadSignIsComplete =?2 where roadPoleId = ?1")
    public Integer auditRoadSign(Integer roadPoleIds, Integer flag) throws Exception;

    /**
     * @Description: 根据标杆Id修改监管单位
     * @Author: shao-yikun
     * @Date: 2017/4/10 20:01
     */
    @Modifying
    @Query(value = "update RoadsignEntity set mainOrgId = ?2, modifierId = ?3 where roadPoleId = ?1")
    public void updateMainOrgIdByRoadPoleId(Integer roadPoleId,
                                            Integer mainOrgId,
                                            Integer modifierId) throws Exception;

    /**
     * @Description: 根据标杆Id修改地区编码
     * @Author: shao-yikun
     * @Date: 2017/4/21 10:28
     */
    @Modifying
    @Query(value = "update RoadsignEntity set roadSignRegionId = ?2, modifierId = ?3 where roadPoleId = ?1")
    public void updateRoadSignRegionIdByRoadPoleId(Integer roadPoleId,
                                                   Integer roadPoleRegionId,
                                                   Integer modifierId) throws Exception;


    public List<RoadsignDto> getRoadSignAudit4DT(DTRequestDto dtRequestDto, Integer roadPoleId) throws Exception;

    @Query(value = "select count(*) from RoadsignEntity " +
            "where roadPoleId = ?1 and roadSignIsComplete = 0")
    public Integer getRoadSignAuditCount4DT(Integer roadPoleId) throws Exception;

    /**
     * @Description: 完成维修后更新存在问题和状态为正常
     * @Author: shao-yikun
     * @Date: 2017/4/12 21:24
     */
    @Modifying
    @Query(value = "update RoadsignEntity set roadSignSecurity = 0, roadSignBug = 0 where roadSignId = ?1")
    public Integer updateRoadSignState(Integer roadSignId) throws Exception;


    @Query(value = "select new com.hualude.roadsign.dto.RoadsignDto(roadSignId, roadPoleId, roadSignDirection, " +
            "roadSignType, roadSignMeaning, roadSignShape, roadSignSize, roadSignPic, roadSignMaterial, " +
            "roadSignInstallType, roadSignFixed, roadSignSecurity, roadSignBug, orgId, createUserId, roadSignRemark, " +
            "roadSignIsComplete, mainOrgId, roadSignLight, environment, defectsLiabilityPeriod, serviceLife, " +
            "installOrg, roadSignInstallTime, roadSignRegionId) " +
            "from RoadsignEntity where roadPoleId = ?1 order by roadSignInstallTime desc")
    public List<RoadsignDto> getSignByRoadPoleId(Integer roadPoleId) throws Exception;

    /**
     * @Description: 根据标牌Id获取标牌信息
     * @Author: shao-yikun
     * @Date: 2017/3/23 16:15
     */
    @Query(value = "select new com.hualude.roadsign.dto.RoadsignDto(rp.projectNumber, rs.roadSignId, rs.roadPoleId, " +
            "rs.roadSignDirection, rs.roadSignType, rs.roadSignPic, rs.roadSignInstallType, " +
            "rs.roadSignSecurity, rs.roadSignRemark, rp.longitude, rp.latitude, rp.province, " +
            "rp.city, rp.area, rp.roadName, rp.startRoad,  rp.endRoad, rp.roadPoleBridge, " +
            "rs.roadSignMeaning, rs.roadSignBug, rs.roadSignFixed, rp.orgId, rp.mainOrgId, rp.roadPolePic, " +
            "rs.roadSignMaterial, rs.defectsLiabilityPeriod, rs.roadSignInstallTime, rs.serviceLife, " +
            "rs.roadSignLight, rs.roadSignShape,rs.roadSignSize, rs.environment, rp.roadPoleShape, " +
            "rp.roadPoleSize, ur.userName, rs.installOrg, rs.roadSignIsComplete)" +
            "from RoadsignEntity rs, RoadpoleEntity rp, UserEntity ur " +
            "where rs.roadPoleId = rp.roadPoleId and ur.userId = rs.createUserId and rs.roadSignId = ?1")
    public RoadsignDto getRoadSignByRoadSignId(Integer roadSignId) throws Exception;

    @Query(value = "select new com.hualude.roadsign.dto.RoadsignDto(rs.roadSignType,rs.roadSignShape,rs.roadSignInstallType," +
            "rs.environment,rs.orgId,rs.mainOrgId,u.userName) from RoadsignEntity rs,UserEntity u where rs.createUserId=u.userId and rs.roadSignId = ?1")
    public RoadsignDto getSignInfoBySignId(Integer roadSignId) throws Exception;


    /**
     * @Description: 根据标杆id获取标牌Id用于显示
     * @Author: xu-jiaHui
     * @Date: 2017/5/16 23:49
     */
    public List<RoadsignDto> getRoadSignCollect(Integer roadPoleId) throws Exception;

    @Modifying
    @Query(value = "update RoadsignEntity set  roadSignIsComplete =1 where roadSignId = ?1")
    public Integer auditRoadSignById(Integer roadSignId) throws Exception;

    /**
     * @Description: 根据标牌Id获取标杆Id
     * @Author: shao-yikun
     * @Date: 2017/5/6 15:12
     */
    @Query(value = "select roadPoleId from RoadsignEntity where roadSignId = ?1")
    public Integer getRoadPoleIdByRoadSignId(Integer roadSignId) throws Exception;

    /**
     * @Description: 获取标杆对应所有标牌的安全状态的和
     * @Author: shao-yikun
     * @Date: 2017/5/6 15:22
     */
    @Query(value = "select sum(roadSignSecurity) from RoadsignEntity where roadPoleId = ?1")
    public Integer getRoadSignSecuritySumByRoadPoleId(Integer roadPoleId) throws Exception;

    /**
     * @Description: 故障上报后修改标杆标牌的安全状态
     * @Author: xu-jiaHui
     * @Date: 2017/5/6 22:35
     */
    @Modifying
    @Query(value = "update RoadsignEntity set roadSignSecurity=1 where roadSignId=?1")
    public Integer updateRoadSignSecurity(Integer roadSignId) throws Exception;


    /**
     * @Description: 标牌所属的公司
     * @Author: shao-yikun
     * @Date: 2017/5/21 0:06
     */
    @Query(value = "select orgId from RoadsignEntity where roadSignId = ?1")
    public Integer getOrgIdByRoadSignId(Integer roadSignId) throws Exception;
}
